﻿Imports System.Data.OleDb


Partial Public Class details
    Inherits System.Web.UI.Page

    Dim conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("PlacesConnectionString").ToString())


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        countViews()
        createTagCloud()
        If IsPostBack Then
            rateLocation(conn, User.Identity.Name, Rating1.CurrentRating, Session("locationId"))
        Else
            Session("locationId") = Request.QueryString("ID")
        End If

        Rating1.CurrentRating = getUserRating(conn, Session("locationID"), User.Identity.Name)
        Rating2.CurrentRating = getAverageUserRating(conn, Session("locationID"))
    End Sub


    Sub countViews()
        Dim locationViews = Request.QueryString("ID")
        If Not IsPostBack Then
            Dim viewCount As Integer = 0
            'Dim conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("PlacesConnectionString").ToString())
            Dim readCountSQL As String = "SELECT Views FROM locations where ID =" & locationViews
            Dim cmd As New OleDbCommand(readCountSQL, conn)
            conn.Open()
            Dim reader As OleDbDataReader = cmd.ExecuteReader()
            If (reader.Read()) Then
                viewCount = (reader("Views"))
            Else
                viewCount = 0
            End If

            viewCount += 1
            conn.Close()

            Dim updateCountSQL As String = "UPDATE locations SET Views =" + viewCount.ToString() & " WHERE ID =" & locationViews

            conn.Open()
            Dim updateCountCMD As New OleDbCommand(updateCountSQL, conn)
            updateCountCMD.ExecuteNonQuery()
            conn.Close()
        End If

    End Sub



    Protected Sub btn_submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_submit.Click
        Dim id = Request.QueryString("ID")
        'Dim conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("PlacesConnectionString").ToString())

        conn.Open()

        Dim sql As String = "INSERT INTO posting(Comment,Location_fkID,Author) VALUES(@Comment,@Location_fkID,@Author)"

        Dim cmd As New OleDbCommand(sql, conn)
        cmd.Parameters.AddWithValue("@Comment", tb_comment.Text)
        cmd.Parameters.AddWithValue("@Location_fkID", id)
        cmd.Parameters.AddWithValue("@Author", User.Identity.Name)
        cmd.Parameters.AddWithValue("@DatePosted", DateTime.Now)
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        conn.Close()

        ''ListView1.DataSource = SqlDataSource2
        ListView1.DataBind()
    End Sub

    Protected Sub rateLocation(ByVal conn As OleDbConnection, ByVal userName As String, ByVal score As Integer, ByVal locationId As Integer)
        conn.Open()
        Dim DeletePreviousRatingSql As String = "Delete * FROM ratings WHERE userIdentity = @userIdentity AND Area_fkid = @Area_fkid"
        Dim DeletePreviousRatingCmd As New OleDbCommand(DeletePreviousRatingSql, conn)
        DeletePreviousRatingCmd.Parameters.AddWithValue("@userIdentity", User.Identity.Name)
        DeletePreviousRatingCmd.Parameters.AddWithValue("@Area_fkid", locationId)
        DeletePreviousRatingCmd.ExecuteNonQuery()

        Dim sql2 As String = "INSERT INTO ratings (userIdentity, score, Area_fkid) VALUES(@userIdentity, @score, @Area_fkid)"
        Dim cmd As New OleDbCommand(sql2, conn)
        cmd.Parameters.AddWithValue("@userIdentity", User.Identity.Name)
        cmd.Parameters.AddWithValue("@score", Rating1.CurrentRating)
        cmd.Parameters.AddWithValue("@Area_fkid", locationId)
        cmd.ExecuteNonQuery()
        conn.Close()

    End Sub




    'Protected Sub btn_Rate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Rate.Click
    'Dim id = Request.QueryString("ID")
    'Dim conn As New OleDbConnection(ConfigurationManager.ConnectionStrings("PlacesConnectionString").ToString())

    ' End Sub

    Protected Function getAverageUserRating(ByVal conn As OleDbConnection, ByVal id As Integer)
        Dim averageSQL As String = "SELECT AVG(score) as RatingAverage FROM ratings WHERE Area_fkid = @Area_fkid"
        Dim averageCmd As New OleDbCommand(averageSQL, conn)
        averageCmd.Parameters.AddWithValue("@Area_fkid", id)
        Dim averageDBRead As OleDbDataReader = averageCmd.ExecuteReader()
        averageDBRead.Read()
        Try
            Return CInt(averageDBRead("RatingAverage"))
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Protected Function getUserRating(ByVal conn As OleDbConnection, ByVal id As Integer, ByVal userName As String)
        Dim userRatingSql As String = "SELECT * FROM ratings WHERE Area_fkid = @Area_fkid AND userIdentity = @userIdenity"
        Dim userRatingCmd As New OleDbCommand(userRatingSql, conn)
        conn.Open()
        userRatingCmd.Parameters.AddWithValue("@Area_fkid", id)
        userRatingCmd.Parameters.AddWithValue("@userIdentity", User.Identity.Name)
        Dim userRatingReader As OleDbDataReader = userRatingCmd.ExecuteReader()
        userRatingReader.Read()
        Try
            Return CInt(userRatingReader("score"))
        Catch ex As Exception
            Return 0
        End Try
    End Function


    Sub setRating()
        Dim id = Request.QueryString("ID")
        Dim userIdentity As String = User.Identity.Name
        Rating1.CurrentRating = getUserRating(conn, id, userIdentity)
        Rating2.CurrentRating = getAverageUserRating(conn, id)
        conn.Close()
    End Sub


    Protected Sub btn_AddTag_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_AddTag.Click
        Dim allTheTags As Array = getAllTags()
        Dim newTags() As String = tb_AddTag.Text.Split(",")
        For Each nTag As String In newTags
            If doesTagExist(allTheTags, nTag) = True Then Continue For
            If nTag.Length > 2 Then
                addTagToTagTable(nTag)
                Dim lastTagId = CInt(getLastTagAdded())
                Dim lastLocationId = CInt(Request.QueryString("ID"))
                addTagToLocation(lastLocationId, lastTagId)
            End If
            'Response.Write(getLastTagAdded())
            CheckBoxList_TagList.DataBind()

        Next
    End Sub

    Protected Sub btn_Tag_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btn_Tag.Click
        Dim id = Request.QueryString("ID")

        For Each item As ListItem In CheckBoxList_TagList.Items
            If item.Selected Then
                addTagToLocation(id, item.Value)
            End If
        Next
    End Sub

    Function getLastTagAdded()
        Dim sql As String = "SELECT TOP 1 ID FROM Tags ORDER BY ID DESC"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        conn.Open()
        Dim dbread = cmd.ExecuteReader()
        dbread.Read()
        Dim returnVal = dbread("ID")
        conn.Close()
        Return returnVal
    End Function

    Function getAllTags()
        Dim allTags As New List(Of String)

        Dim sql As String = "SELECT TagName FROM Tags"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        conn.Open()
        Dim dbread = cmd.ExecuteReader()
        While (dbread.Read())
            allTags.Add(dbread("TagName"))
        End While
        conn.Close()
        Return allTags.ToArray()
    End Function

    Function doesTagExist(ByVal tagArray As Array, ByVal newTag As String)
        For Each oldTag As String In tagArray
            If oldTag.ToLower = newTag.ToLower Then
                Return True
            End If
        Next
        Return False
    End Function


    Sub addTagToLocation(ByVal locationID As Integer, ByVal tagId As Integer)
        Dim sql As String = "INSERT INTO TagLocationJunction (Location_fkid, Tag_fkid) VALUES (@Location_fkid, @Tag_fkid)"
        Dim cmd As OleDbCommand = New OleDbCommand(sql, conn)
        cmd.CommandType = CommandType.Text
        conn.Open()
        cmd.Parameters.AddWithValue("@Location_fkid", locationID)
        cmd.Parameters.AddWithValue("@Tag_fkid", tagId)
        cmd.ExecuteNonQuery()
        conn.Close()
    End Sub


    Sub addTagToTagTable(ByVal newTag As String)
        Dim sql As String = "INSERT INTO Tags (TagName) VALUES(@TagName)"
        Dim cmd As New OleDbCommand(sql, conn)
        cmd.CommandType = CommandType.Text
        cmd.Parameters.AddWithValue("@TagName", newTag)
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
    End Sub

    Sub createTagCloud()
        Dim tagCountSQL As String = "SELECT LocationsByTag.TagName, Count(*) AS TagCount FROM(LocationsByTag)GROUP BY LocationsByTag.TagName"
        conn.Open()
        Dim tagCountCmd As New OleDbCommand(tagCountSQL, conn)
        Dim tagCountReader = tagCountCmd.ExecuteReader()
        Dim tagHash As New Hashtable
        Dim maxTagCount As Integer = 0
        Dim minTagCount As Integer = 0
        If tagCountReader.HasRows() Then

            While tagCountReader.Read()
                'Response.Write(tagCountReader("TagName") & " " & tagCountReader("TagCount"))
                tagHash.Add(tagCountReader("TagName"), CInt(tagCountReader("TagCount")))
                If minTagCount = 0 Then minTagCount = CInt(tagCountReader("TagCount"))
                If minTagCount > CInt(tagCountReader("TagCount")) Then minTagCount = CInt(tagCountReader("TagCount"))
                If maxTagCount < CInt(tagCountReader("TagCount")) Then maxTagCount = CInt(tagCountReader("TagCount"))
            End While
        End If

        Dim fontOptions() As String = {"1em", "2em", "3em", "4em", "4.5em", "5em", "5.5em"}
        Dim scaleUnit As Decimal = (maxTagCount - minTagCount + 1) / Convert.ToDecimal(fontOptions.Length)
        Dim tagCloudHtml As String = ""
        Dim homePageStem As String = "ShowLocationsByTag.aspx?"
        For Each tempTag In tagHash
            Dim scaleValue As Integer = Math.Truncate((tempTag.Value - minTagCount) / scaleUnit)
            tagCloudHtml = tagCloudHtml & String.Format("<A HREF = ""{0}"" style = ""color:Red; Font-Size:{1};"">{2}</A>", homePageStem & "tag=" & tempTag.key, fontOptions(scaleValue), tempTag.key) & " "
        Next
        placeHolder_TagCloud.Controls.Add(New LiteralControl(tagCloudHtml))

        conn.Close()
    End Sub




End Class